package com.wanmait.shoppingsys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.wanmait.shoppingsys.util.DbHelper;
import com.wanmait.shoppingsys.vo.Address;
import com.wanmait.shoppingsys.vo.UserInfo;

public class AddressDAO {
	  private static AddressDAO addressDAO;
	  
	  private AddressDAO() {
		  
	  }
	  public static AddressDAO getDAO() {
		  if(addressDAO==null) {
			  addressDAO=new AddressDAO();
		  }
		  return addressDAO;
	  }
	  
	  public void delete(Integer id) {  //删除地址
			String sql = "DELETE from address Where id=?";
			Connection conn=  DbHelper.getConn();
			PreparedStatement ps=null;
			
			try {
					ps=  conn.prepareStatement(sql);
					ps.setInt(1, id);
					ps.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
				// TODO: handle exception
			}
			finally
			{
				DbHelper.close(ps, conn);
			}
		
			
			
		}
	  
	  public void insert(Address address) {  //添加地址
			String sql = "INSERT into address(namea,site,cpn,userinfo_id,province,city,district)VALUES(?,?,?,?,?,?,?)";
			ArrayList params = new ArrayList();
			params.add(address.getNamea());
			params.add(address.getSite());
			params.add(address.getCpn());
			params.add(address.getId());
			params.add(address.getProvince());
			params.add(address.getCity());
			params.add(address.getDistrict());
		
			
			DbHelper.update(sql, params);
		}
	  public ArrayList<Address> findAll(Integer id) {
		  	ArrayList params=new ArrayList();
			String sql="SELECT address.* FROM address LEFT JOIN userinfo ON address.userinfo_id=userinfo.id WHERE userinfo.id=?";
			 Connection conn=  DbHelper.getConn();
			 PreparedStatement ps=null;
			 ResultSet rs=null;
			 
			    try {
					ps  = conn.prepareStatement(sql);
					ps.setInt(1, id);
					 rs = ps.executeQuery();
					  while(rs.next())
					  {
						Address address=new Address();
						 address.setNamea(rs.getString("namea"));//名字
						 address.setSite(rs.getString("site"));//地址
						 address.setCpn(rs.getString("cpn"));//手机号
						 address.setId(rs.getInt("id"));//用户id
						 address.setProvince(rs.getString("province"));
						 address.setCity(rs.getString("city"));
						 address.setDistrict(rs.getString("district"));
						 params.add(address);
						  
					  }
					
				} catch (SQLException e) {
					
					e.printStackTrace();
				}
			    finally {
			       DbHelper.close(rs, ps, conn);
			    }
			
			
			
			return params;
		  
		  
	  }
	  
}
